﻿Imports System.Xml
Imports System.Text
Imports System.Data.SqlClient

Public Class SAPFunctions
    Public Shared Function ConnectSAPDB(ByVal ServerName As String, ByVal CompanyDB As String, _
                                 ByVal DbServerType As String, ByVal DbUserName As String, _
                                 ByVal DbPassword As String, ByVal UserName As String, _
                                 ByVal Password As String, ByVal LicenseServer As String) As String
        Dim lRetCode As Integer
        Dim lErrCode As Integer
        Dim sErrMsg As String
        Try
            If PublicVariable.oCompanyInfo.Connected Then
                PublicVariable.oCompanyInfo.Disconnect()
            End If

            PublicVariable.oCompanyInfo.Server = ServerName
            PublicVariable.oCompanyInfo.DbUserName = CompanyDB
            PublicVariable.oCompanyInfo.DbPassword = DbUserName
            PublicVariable.oCompanyInfo.LicenseServer = LicenseServer
            PublicVariable.oCompanyInfo.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MSSQL2008

            PublicVariable.oCompanyInfo.CompanyDB = CompanyDB
            PublicVariable.oCompanyInfo.UserName = UserName
            PublicVariable.oCompanyInfo.Password = Password

            lRetCode = PublicVariable.oCompanyInfo.Connect
            If lRetCode <> 0 Then
                PublicVariable.oCompanyInfo.GetLastError(lErrCode, sErrMsg)
                SystemFunction.WriteLog(sErrMsg)
                Return sErrMsg
            Else
                Return ""
            End If
        Catch ex As Exception
            SystemFunction.WriteLog(sErrMsg)
            Return sErrMsg
        End Try
    End Function
    Private Shared Function ToXMLStringFromDS(ObjType As String, ds As DataSet) As [String]
        Try
            'Dim gf As New GeneralFunctions()
            Dim XmlString As New StringBuilder()
            Dim writer As XmlWriter = XmlWriter.Create(XmlString)
            writer.WriteStartDocument()
            If True Then
                writer.WriteStartElement("BOM")
                If True Then
                    writer.WriteStartElement("BO")
                    If True Then
                        '#Region "write ADMINFO_ELEMENT"
                        writer.WriteStartElement("AdmInfo")
                        If True Then
                            writer.WriteStartElement("Object")
                            If True Then
                                writer.WriteString(ObjType)
                            End If
                            writer.WriteEndElement()
                        End If
                        writer.WriteEndElement()
                        '#End Region

                        '#Region "Header&Line XML"
                        For Each dt As DataTable In ds.Tables
                            writer.WriteStartElement(dt.TableName.ToString())
                            If True Then
                                For Each row As DataRow In dt.Rows
                                    writer.WriteStartElement("row")
                                    If True Then
                                        For Each column As DataColumn In dt.Columns
                                            If column.DefaultValue.ToString() <> "xx_remove_xx" Then
                                                If column.ColumnName <> "No" Then
                                                    'phan lon cac table deu co column No nay
                                                    If row(column).ToString() <> "" Then
                                                        writer.WriteStartElement(column.ColumnName)
                                                        'Write Tag
                                                        If True Then
                                                            writer.WriteString(row(column).ToString())
                                                        End If
                                                        writer.WriteEndElement()
                                                    End If
                                                End If
                                            End If
                                        Next
                                    End If
                                    writer.WriteEndElement()
                                Next
                            End If
                            writer.WriteEndElement()
                        Next
                        '#End Region
                    End If
                    writer.WriteEndElement()
                End If
                writer.WriteEndElement()
            End If
            writer.WriteEndDocument()

            writer.Flush()

            Return XmlString.ToString()
        Catch ex As Exception
            Return ex.ToString()
        End Try
    End Function
    Private Shared Function CreateMarketingDocument(ByVal strXml As String, DocType As String) As String
        Try
            Dim sStr As String = "Operation Completed Successfully!"
            Dim lRetCode As Integer
            Dim lErrCode As Integer
            Dim sErrMsg As String
            Dim oDocment
            Select Case DocType
                Case "30"
                    oDocment = DirectCast(oDocment, SAPbobsCOM.JournalEntries)
                Case "97"
                    oDocment = DirectCast(oDocment, SAPbobsCOM.SalesOpportunities)
                Case "191"
                    oDocment = DirectCast(oDocment, SAPbobsCOM.ServiceCalls)
                Case "33"
                    oDocment = DirectCast(oDocment, SAPbobsCOM.Contacts)
                Case "221"
                    oDocment = DirectCast(oDocment, SAPbobsCOM.Attachments2)
                Case "2"
                    oDocment = DirectCast(oDocment, SAPbobsCOM.BusinessPartners)

                Case Else
                    oDocment = DirectCast(oDocment, SAPbobsCOM.Documents)
            End Select

            PublicVariable.oCompanyInfo.XMLAsString = True
            oDocment = PublicVariable.oCompanyInfo.GetBusinessObjectFromXML(strXml, 0)

            lErrCode = oDocment.Add()

            If lErrCode <> 0 Then
                PublicVariable.oCompanyInfo.GetLastError(lErrCode, sErrMsg)
                Return sErrMsg
            Else
                Return ""
            End If

        Catch ex As Exception
            Return ex.ToString
        End Try
    End Function
    Public Shared Sub Syn_AllBill()
        Dim dt As DataTable = BLL.POSRunQuery("Select * From Bill Where BILL_StatusCode=2 and ISNULL(IsSyn,0)=0")
        For i As Integer = 0 To dt.Rows.Count - 1
            SynBill(dt.Rows(i).Item("BILL_CODE").ToString())
        Next
    End Sub
    Private Shared Sub SynBill(Bill_Code)
        'Dim dtHeader As New DataTable
        'dtHeader.Columns.Add("CardCode")
        'dtHeader.Columns.Add("DocDate")

        'Dim dtRow As New DataTable
        'dtRow.Columns.Add("ItemCode")
        'dtRow.Columns.Add("Dscription")
        'dtRow.Columns.Add("UnitPrice")
        'dtRow.Columns.Add("Quantity")

        'dtRow.Rows.Add()
        'Dim str As String
        'Dim ds As New DataSet
        'ds.Tables.Add(dtHeader.Copy())
        'ds.Tables.Add(dtRow.Copy())
        'str = ToXMLStringFromDS("13", ds)
        'CreateMarketingDocument(str, "13")
        Try
            Dim RetVal As Long
            Dim str As String = ""
            Dim lErrCode As Integer
            Dim sErrMsg As String
            Dim dtBill As DataTable
            dtBill = BLL.POSRunQuery("Select * from Bill where Bill_Code=" + CStr(Bill_Code))

            Dim oDraft As SAPbobsCOM.Documents
            oDraft = PublicVariable.oCompanyInfo.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oInvoices)
            oDraft.DocDate = dtBill.Rows(0).Item("Bill_Date")
            oDraft.CardCode = dtBill.Rows(0).Item("CUST_Code")
            oDraft.TrackingNumber = CStr(Bill_Code)

            Dim dtBill_Detail As DataTable
            dtBill_Detail = BLL.POSRunQuery("Select * from billdetail where Bill_Code=" + CStr(Bill_Code))

            For i As Integer = 0 To dtBill_Detail.Rows.Count - 1
                If i > 0 Then
                    oDraft.Lines.Add()
                End If
                oDraft.Lines.ItemCode = dtBill_Detail.Rows(i).Item("Item_Code")
                oDraft.Lines.ItemDescription = dtBill_Detail.Rows(i).Item("Item_Name")
                oDraft.Lines.Quantity = dtBill_Detail.Rows(i).Item("BDET_Qty")
                oDraft.Lines.Price = dtBill_Detail.Rows(i).Item("Item_Price")
                oDraft.Lines.WarehouseCode = "01"
            Next
            RetVal = oDraft.Add
            If RetVal <> 0 Then
                PublicVariable.oCompanyInfo.GetLastError(lErrCode, sErrMsg)
                SystemFunction.WriteLog(sErrMsg)
            Else
                str = "Update Bill Set IsSyn=1 where BILL_Code=" + CStr(Bill_Code)
                BLL.POSRunQuery(str)
            End If
            ' Return str
        Catch ex As Exception
            SystemFunction.WriteLog(ex.ToString)
            'Return ex.ToString
        End Try
    End Sub
    Public Shared Sub Syn_AllBill_Payment()
        Dim dt As DataTable = BLL.POSRunQuery("Select * From Bill Where BILL_StatusCode=2 and ISNULL(IsSyn,0)=1")
        For i As Integer = 0 To dt.Rows.Count - 1
            SynBill(dt.Rows(i).Item("BILL_CODE").ToString())
        Next
    End Sub
    Public Function Integration_IncomingPayment(ByVal Bill_Code As Integer)
        Dim str As String = ""
        Dim RetVal As Long
        Dim lErrCode As Integer
        Dim sErrMsg As String
        Dim TagetJE As SAPbobsCOM.Payments
        Dim dtBill As DataTable

        dtBill = BLL.POSRunQuery("Select * from Bill where Bill_Code=" + CStr(Bill_Code))
        TagetJE = PublicVariable.oCompanyInfo.GetBusinessObject(SAPbobsCOM.BoObjectTypes.oIncomingPayments)
        TagetJE.CardCode = dtBill.Rows(0).Item("CUST_Code")
        'TagetJE.CashAccount = MapCashAct_POS2SAP(-1)
        TagetJE.DocDate = dtBill.Rows(0).Item("Bill_Date")
        TagetJE.TaxDate = dtBill.Rows(0).Item("Bill_Date")

        TagetJE.Invoices.DocEntry = MapAR_POS2SAP(Bill_Code)
        TagetJE.Invoices.InvoiceType = SAPbobsCOM.BoRcptInvTypes.it_Invoice
        TagetJE.CashSum = dtBill.Rows(0).Item("Bill_Amt")
        RetVal = TagetJE.Add
        If RetVal <> 0 Then
            PublicVariable.oCompanyInfo.GetLastError(lErrCode, sErrMsg)
            str = sErrMsg
        End If
        Return str
    End Function
    Public Shared Sub GetItemMasterFromSAP()
        Dim dt As DataTable = SAPRunQuery("select * from integration where DocumentType='2' and Status=<>1")
        For i As Integer = 0 To dt.Rows.Count - 1
            Dim dt1 As DataTable = SAPRunQuery("select * from OITM where Itemcode='" + dt.Rows(i).Item("DocumentNo").ToString + "'")
            If dt1.Rows.Count > 0 Then
                'BLL.Object_Update_NonResult(
            End If
        Next

    End Sub
    Private Function MapAR_POS2SAP(ByVal Bill_Code As Integer) As Integer
        Dim str As String
        str = "Select DocEntry from OINV where TrackNo=" + CStr(Bill_Code)
        Dim dt As DataTable
        dt = SAPRunQuery(str)
        If dt.Rows.Count > 0 Then
            Return dt.Rows(0).Item("DocEntry").ToString
        Else
            Return 0
        End If
    End Function

#Region "SAP ADO REGION"
    Public Shared Function SAPRunQuery(ByVal querystr As String) As DataTable
        Try
            If OpenSQLConnection() Then
                Dim MyCommand As SqlCommand = New SqlCommand(querystr, PublicVariable.Connection)
                MyCommand.CommandType = CommandType.Text
                Dim da As SqlDataAdapter = New SqlDataAdapter()
                Dim mytable As DataTable = New DataTable()
                da.SelectCommand = MyCommand
                da.Fill(mytable)
                PublicVariable.Connection.Close()
                If mytable Is Nothing Then Return Nothing
                Return mytable
            Else
                Return Nothing
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Return Nothing
        End Try
    End Function
    Public Shared Function OpenSQLConnection() As Boolean
        PublicVariable.Connection = New SqlConnection
        PublicVariable.Connection.ConnectionString = PublicVariable.SAPConnectionString
        If PublicVariable.Connection.State = ConnectionState.Open Then
            PublicVariable.Connection.Close()
        End If
        Try
            PublicVariable.Connection.Open()
        Catch ex As Exception
            'MessageBox.Show(ex.Message)
            Return False
        End Try
        Return True
    End Function
#End Region
End Class